package com.daotingli.backendprograms.Repository.Admin.Adminherd;

import com.daotingli.backendprograms.Bean.Sum.*;
import com.daotingli.backendprograms.DB.SpringDB;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import  java.util.List;
@Repository
public class AdminHerdRepository {

    @Autowired
    private SpringDB springDB;

    @Autowired
    private identity_Sum idnitySumBean;
    @Autowired
    private Author_Book_Sum author_book_sum;
    @Autowired
    private Author_id_Book_Sum author_id_book_sum;
    @Autowired
    private Book_Type_Sum book_type_sum;
    @Autowired
    private Book_id_type_Sum book_id_type_sum;
    @Autowired
    private Book_tag_Sum book_tag_sum;
    @Autowired
    private Book_id_Name_Sum book_id_name_sum;
    public List<identity_Sum> getAllHerds() {
        String sql  = "select identity,count(*) as sum from t_user group by identity";
        String sql1 = "select identity from t_identity";
        List<identity_Sum> list = new ArrayList<>();
        List<String> list1 = new ArrayList<>();
        try {
            springDB.getJdbcTemplate().query(sql, (rs, rowNum) -> {
                idnitySumBean = new identity_Sum(rs.getString("identity"), rs.getInt("sum"));
                list.add(idnitySumBean);
                return idnitySumBean;
            });
            springDB.getJdbcTemplate().query(sql1, (rs, rowNum) -> {
                list1.add(rs.getString("identity"));
                return rs.getString("identity");
            });
            for (int i = 0; i < list1.size(); i++) {
                if (!list.contains(list1.get(i))) {
                    list.get(i).setIdentity(list1.get(i));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return  list;
    }
    public List<Author_Book_Sum> getBookAuthorSum() {
        // 从book表中间查询 作者id 以及作者完成的书籍数量
        String sql = "select author_id,count(*) as sum from book group by author_id";
        // 从t_user表中间查询 作者的姓名
        String sql1 = "select id,username from t_user where identity = '2'";
        List<Author_Book_Sum> list = new ArrayList<>();
        List<Author_id_Book_Sum> list1 = new ArrayList<>();
        try {
            springDB.getJdbcTemplate().query(sql, (rs, rowNum) -> {
                author_book_sum = new Author_Book_Sum(rs.getString("author_id"), rs.getInt("sum"));
                list.add(author_book_sum);
                return author_book_sum;
            });
            springDB.getJdbcTemplate().query(sql1, (rs, rowNum) -> {
                author_id_book_sum = new Author_id_Book_Sum(rs.getString("id"), rs.getString("username"));
                list1.add(author_id_book_sum);
                return author_id_book_sum;
            });
            for (int i = 0; i < list.size(); i++) {
                for (int j = 0; j < list1.size(); j++) {
                    if (list.get(i).getAuthor().equals(list1.get(j).getAuthor_id())) {
                        list.get(i).setAuthor(list1.get(j).getUsername());
                    }
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }



        return list;
    }

    public List<Book_Type_Sum> getBookTypeSum() {
        String sql = "select booktypeid,count(*) as sum from book_type_view group by booktypeid";
        String sql1 = "select id,type from book_type";
        List<Book_Type_Sum> list = new ArrayList<>();
        List<Book_id_type_Sum> list1 = new ArrayList<>();
        try {
            springDB.getJdbcTemplate().query(sql, (rs, rowNum) -> {
                book_type_sum = new Book_Type_Sum(rs.getString("booktypeid"), rs.getInt("sum"));
                list.add(book_type_sum);
                return book_type_sum;
            });
            springDB.getJdbcTemplate().query(sql1, (rs, rowNum) -> {
                book_id_type_sum = new Book_id_type_Sum(rs.getInt("id"), rs.getString("type"));
                list1.add(book_id_type_sum);
                return book_id_type_sum;
            });


            for(int i=0;i<list.size();i++){
                for(int j=0;j<list1.size();j++){
                    if(list.get(i).getType().equals(list1.get(j).getId()+"")){
                       list.get(i).setType(list1.get(j).getType());


                    }
                }
            }




        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    public List<Book_tag_Sum> getBookTagSum() {
        String sql = "select bookid,count(*) as sum from book_type_view group by bookid";
        String sql1 = "select id,book_name from book";

        List<Book_tag_Sum> list = new ArrayList<>();
        List<Book_id_Name_Sum> list1 = new ArrayList<>();
        try {
            springDB.getJdbcTemplate().query(sql, (rs, rowNum) -> {
                book_tag_sum = new Book_tag_Sum(rs.getString("bookid"), rs.getInt("sum"));
                list.add(book_tag_sum);
                return book_tag_sum;
            });
            springDB.getJdbcTemplate().query(sql1, (rs, rowNum) -> {
                book_id_name_sum = new Book_id_Name_Sum(rs.getInt("id"), rs.getString("book_name"));
                list1.add(book_id_name_sum);
                return book_id_name_sum;
            });
            for(int i=0;i<list.size();i++){
                for(int j=0;j<list1.size();j++){
                    if(list.get(i).getBookName().equals(list1.get(j).getBookId()+"")){
                        list.get(i).setBookName(list1.get(j).getBookName());
                    }
                }
            }
        }
        catch (Exception e) {
            throw new RuntimeException(e);
        }


        return list;
    }
}
